import asyncio
from googletrans import Translator
import pandas as pd


async def translate_text(text, src='en', dest='zh-CN'):
    try:
        translator = Translator()
        translation = await translator.translate(text, src=src, dest=dest)
        return translation.text
    except Exception as e:
        return text


async def process_data(selected_columns):
    # 翻译Summary列
    tasks_summary = [translate_text(summary) for summary in selected_columns['Summary']]
    results_summary = await asyncio.gather(*tasks_summary)
    selected_columns['Summary_chinese'] = results_summary

    # 归纳并翻译Description列
    def summarize(description):
        if pd.notnull(description):
            return description[:200] if len(description) > 200 else description
        return description

    tasks_description = [translate_text(summarize(desc)) for desc in selected_columns['Description']]
    results_description = await asyncio.gather(*tasks_description)
    selected_columns['Description_chinese'] = results_description

    return selected_columns

# 加载数据集
df = pd.read_csv('ASF JIRA 2025-09-24T15_50_48+0800.csv')

# 提取需要的列
selected_columns = df[['Summary', 'Resolution', 'Affects Version/s', 'Description']]

# 运行异步任务
loop = asyncio.get_event_loop()
selected_columns = loop.run_until_complete(process_data(selected_columns))

# 保存为 Excel 文件
selected_columns.to_excel('ASF_JIRA_selected_columns_translated.xlsx', index=False)